package io;

import java.awt.image.BufferedImage;
import java.io.ByteArrayOutputStream;
import java.io.File;
import java.io.IOException;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.Locale;
import java.util.Vector;

import javax.imageio.ImageIO;

import jxl.CellView;
import jxl.Workbook;
import jxl.WorkbookSettings;
import jxl.format.Border;
import jxl.format.BorderLineStyle;
import jxl.format.UnderlineStyle;
import jxl.write.Formula;
import jxl.write.Label;
import jxl.write.Number;
import jxl.write.NumberFormat;
import jxl.write.NumberFormats;
import jxl.write.WritableCellFormat;
import jxl.write.WritableFont;
import jxl.write.WritableImage;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;
import jxl.write.biff.RowsExceededException;


public class WriteExcel {

	private WritableCellFormat timesBoldUnderline;
	private WritableCellFormat times;
	private static final double CELL_DEFAULT_HEIGHT = 17;
	private static final double CELL_DEFAULT_WIDTH = 64;
	


	public void createReport(String name,Vector<Integer> values) throws IOException, WriteException {
		SimpleDateFormat formateador = new SimpleDateFormat("dd-MM-yy");
		File file = new File(name+formateador.format(new Date())+".xls");
		WorkbookSettings wbSettings = new WorkbookSettings();
		wbSettings.setLocale(new Locale("es", "ES"));
		WritableWorkbook workbook = Workbook.createWorkbook(file, wbSettings);
		workbook.createSheet("Reporte", 0);
		WritableSheet excelSheet = workbook.getSheet(0);
		createLabel(excelSheet);
		createContent(excelSheet, values);
		setFormatBorder(excelSheet);
		workbook.write();
		workbook.close();
	}

	private void setFormatBorder(WritableSheet sheet) {
		try {
		NumberFormat por = new NumberFormat("0.00%"); 
		WritableCellFormat porcentaje = new WritableCellFormat(por); 
		porcentaje.setBorder(Border.ALL, BorderLineStyle.THIN);
		
		WritableCellFormat numero = new WritableCellFormat(NumberFormats.INTEGER);
		numero.setBorder(Border.ALL, BorderLineStyle.THIN);
		
		WritableFont arial10font = new WritableFont(WritableFont.ARIAL, 10,WritableFont.BOLD); 
		WritableCellFormat texto = new WritableCellFormat(arial10font);
		texto.setBorder(Border.ALL, BorderLineStyle.THIN);
		

		File imageFile = new File("repositorio/logo.png");
		BufferedImage input;
		input = ImageIO.read(imageFile);
		ByteArrayOutputStream baos = new ByteArrayOutputStream();
		ImageIO.write(input, "PNG", baos);
		sheet.addImage(new WritableImage(1,1,input.getWidth() / CELL_DEFAULT_WIDTH,
		    input.getHeight() / CELL_DEFAULT_HEIGHT,baos.toByteArray()));

		
		
		sheet.getWritableCell(1,6).setCellFormat(texto);
		sheet.getWritableCell(2,6).setCellFormat(texto);
		sheet.getWritableCell(3,6).setCellFormat(texto);
		sheet.getWritableCell(4,6).setCellFormat(texto);
		sheet.getWritableCell(5,6).setCellFormat(texto);
		
		sheet.getWritableCell(1,7).setCellFormat(numero);
		sheet.getWritableCell(2,7).setCellFormat(numero);
		sheet.getWritableCell(3,7).setCellFormat(numero);
		sheet.getWritableCell(4,7).setCellFormat(porcentaje);
		sheet.getWritableCell(5,7).setCellFormat(porcentaje);
		
		
		
		
		} catch (WriteException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}

	private void createLabel(WritableSheet sheet)
			throws WriteException {
		// Lets create a times font
		WritableFont times10pt = new WritableFont(WritableFont.ARIAL, 10);
		// Define the cell format
		times = new WritableCellFormat(times10pt);
		// Lets automatically wrap the cells
		times.setWrap(true);

		// Create create a bold font with unterlines
		WritableFont times10ptBoldUnderline = new WritableFont(
				WritableFont.TIMES, 10, WritableFont.BOLD, false,
				UnderlineStyle.SINGLE);
		timesBoldUnderline = new WritableCellFormat(times10ptBoldUnderline);
		// Lets automatically wrap the cells
		timesBoldUnderline.setWrap(true);

		CellView cv = new CellView();
		cv.setFormat(times);
		cv.setFormat(timesBoldUnderline);
		cv.setAutosize(true);

		// Write a few headers
		addCaption(sheet, 1, 6, "Horas Totales");
		addCaption(sheet, 2, 6, "Horas Inactivas");
		addCaption(sheet, 3, 6, "Horas Activas");
		addCaption(sheet, 4, 6, "Cumplido");
		addCaption(sheet, 5, 6, "Desvio");
		

	}

	private void createContent(WritableSheet sheet, Vector<Integer> values) throws WriteException,
			RowsExceededException {
		
		addNumber(sheet, 1, 7, values.get(0));
		addNumber(sheet, 2, 7, values.get(1));
		addNumber(sheet, 3, 7, values.get(2));
		
		
		StringBuffer buf = new StringBuffer();
		buf.append("(D8/B8)");
		Formula f = new Formula(4,7, buf.toString());
		sheet.addCell(f);
		buf = new StringBuffer();
		buf.append("(C8/B8)");
		f = new Formula(5,7, buf.toString());
		sheet.addCell(f);
	}

	private void addCaption(WritableSheet sheet, int column, int row, String s)
			throws RowsExceededException, WriteException {
		Label label;
		label = new Label(column, row, s, timesBoldUnderline);
		sheet.addCell(label);
	}

	private void addNumber(WritableSheet sheet, int column, int row,
			Integer integer) throws WriteException, RowsExceededException {
		Number number;
		number = new Number(column, row, integer, times);
		sheet.addCell(number);
	}


}